#!/usr/bin/env python

"""

@author wangli
@date 2014-02-10
@version 1.1

"""
import MySQLdb

class table:

    def __init__(self, tablename):
        self.tablename = tablename

    def __getattr__(self, attrname):
        return sqlobj(attrname, self.tablename)

    def inner_join(self, other_table, *on):
        onstr = ' and '.join(['%s' % item for item in on])
        return table('(%s inner join %s on %s)' % (self.tablename, other_table.tablename, onstr))

    def left_join(self, other_table, *on):
        onstr = ' and '.join(['%s' % item for item in on])
        return table('(%s left join %s on %s)' % (self.tablename, other_table.tablename, onstr))


class sqlobj:

    def __init__(self, name, tablename = ''):
        if tablename:
            self.name = '%s.`%s`' % (tablename, name)
        else:
            self.name = '`%s`' % name
        self.str = self.name
        self.tablename = tablename

    def __eq__(self, other):
        if isinstance(other, sqlobj):
            self.str = '%s = %s' % (self.name, other.name)
        elif isinstance(other, str):
            self.str = '%s = \'%s\'' % (self.name, other)
        else:
            self.str = '%s = %s' % (self.name, other)
        return self

    def As(self, name):
        self.str = '%s as `%s`' % (self.name, name)
        self.name = name
        return self

    def __ne__ (self, other):
        if isinstance(other, sqlobj):
            self.str = '%s != %s' % (self.name, other.name)
        elif isinstance(other, str):
            self.str = "%s != `%s`" % (self.name, other)
        else:
            self.str = '%s != %s' % (self.name, other)
        return self
    def __str__(self):
        return self.str

class query:

    def __init__(self, *argv):
        if 0 == len(argv):
            argv = ('*')
        self.argv = argv
        self.sql = ''

    def get_keys_from_argv(self):
        argv = []
        for item in self.argv:
            if isinstance(item, str):
                argv.append(sqlobj(item))
            else:
                argv.append(item)
        keys = ['%s' % key for key in argv]
        return keys

    def select_from(self, tbl):
        ret = self.get_keys_from_argv()
        keys = ','.join(ret)
        self.sql = 'select %s from %s' % (keys, tbl.tablename)
        print self.sql
        return self

    def where(self, *where):
        if not where:
            return self
        tmpwhere = []
        for item in where:
            item = ['%s' % anditem for anditem in item]
            tmpwhere.append(item)
        where = tmpwhere
        andarr = ['(%s)' % ' and '.join(item) for item in where]
        wherestr = ' or '.join(['%s' % item for item in andarr])

        self.sql += ' where %s' % wherestr
        print self.sql
        return self


    def addslashes(self, s):
        l = ["\\", "\"", "\0", "\'"]
        for i in l:
            if i in s:
                s = s.replace(i, '\\'+i)
        return s

    def insert_into(self, tbl, *values):
        if len(values) != len(self.argv):
            print 'insert err values and argv can\'t match'
            return -1
        ret = self.get_keys_from_argv()
        cols = ','.join(ret)
        vals = ['%s' % item for item in values]
        rep = []
        for item in vals:
            item = self.addslashes(item)
            rep.append(item)
        vals = rep
        vals = ','.join(["'%s'" % item for item in vals])
        self.sql = 'insert into %s (%s) values (%s)' % (tbl.tablename, cols, vals)
        print self.sql
        return self

    def update(self, tbl, *values):
        if len(values) != len(self.argv):
            print 'update err values and argv can\'t match'
            return -1
        ret = self.get_keys_from_argv()
        kv = zip(ret, values)
        print kv
        set = ''
        for item in kv:
            print item
            if isinstance(item[1], str):
                tmp = "%s='%s'" % tuple(item)
            else:
                tmp = '%s=%s' % tuple(item)
            if set:
                set += ', '
            set += tmp
        self.sql = 'update %s set %s' % (tbl.tablename, set)
        print self.sql
        return self

    def set_sql(self, sql):
        self.sql = sql
        print self.sql

    def last_insert_id(self):
        self.argv = ['last_insert_id']
        self.sql = 'select last_insert_id()'
        print self.sql

    def execute(self, _engine):
        try:
            self.cursor = _engine.get_cursor()
            self.cursor.execute(self.sql)
        except Exception, e:
            print 'cursor execute error %s' % e

    def fetchall(self):
        ret_keys = []
        for item in self.argv:
            if isinstance(item, sqlobj):
                ret_keys.append(item.name)
            else:
                ret_keys.append(item)
        try:
            for ret in self.cursor.fetchall():
                ret_obj = dict(zip(ret_keys, ret))
                yield ret_obj
        except Exception, e:
            print 'fetch err :', e
            return

    def close(self):
        try:
            self.cursor.close()
        except:
            print 'close cursor failed'

class engine:
    def __init__(self, db_conf):
        self.db_conf = db_conf
        self.conn = self.initMysql()
    def initMysql(self):
        conn=None
        try:
            conn = MySQLdb.connect(**self.db_conf)
            print 'connect sql!'
        except (Exception,), e:
            conn = None
            print 'connect mysql error : %s' % e
        return conn
    def get_cursor(self):
        try:
            cursor = self.conn.cursor()
            cursor.execute('set names utf8')
        except Exception, e:
            cursor = None
            print 'get_cursor err : ', e
        return cursor
if '__main__' == __name__:
    tbl = table('t1')
    q = query('a', 'b')
    q.select_from(tbl).where((tbl.a=='a', tbl.b=='b'))
    q.update(tbl, 1, '2').where((tbl.a=='a',))
